package GUI;

import com.sun.xml.bind.v2.model.core.ID;
import dao.StudentDaoImpl;
import java.lang.String;
import java.awt.BorderLayout;
import java.awt.CardLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Date;
import javax.swing.JOptionPane;
import javax.swing.table.TableModel;
import javax.swing.table.DefaultTableModel;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import module.Student;
import org.apache.poi.hssf.record.CFRuleBase;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import test.BarChart;
import test.TestData;
import util.JDBCUtil;
import util.MyRowMapper;
import javax.swing.filechooser.FileFilter;
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author rog
 */
public class SystemJFrame extends javax.swing.JFrame {
        StudentDaoImpl sdi=new StudentDaoImpl();
        
    
    public SystemJFrame() {
        initComponents();
        updateTable();
        this.setTitle("学生成绩管理系统");
        this.setResizable(false);
    }
    
    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        nouthjPanel = new javax.swing.JPanel();
        titleLabel = new javax.swing.JLabel();
        centerjPanel = new javax.swing.JPanel();
        managePanel = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTable2 = new javax.swing.JTable();
        clearjButton = new javax.swing.JButton();
        deleteSelectedjButton = new javax.swing.JButton();
        allSelectButton = new javax.swing.JButton();
        allUnselectedButton = new javax.swing.JButton();
        addStudentjButton = new javax.swing.JButton();
        queryByIdjButton = new javax.swing.JButton();
        generateTestDatajButton = new javax.swing.JButton();
        generateReportjButton = new javax.swing.JButton();
        queryByNamejButton = new javax.swing.JButton();
        reportPanel = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        reportjTable = new javax.swing.JTable();
        avgjLabel = new javax.swing.JLabel();
        mathjLabel = new javax.swing.JLabel();
        javajLabel = new javax.swing.JLabel();
        sportsjLabel = new javax.swing.JLabel();
        totaljLabel = new javax.swing.JLabel();
        jButton1 = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        titleLabel.setFont(new java.awt.Font("黑体", 0, 36)); // NOI18N
        titleLabel.setText("学生成绩管理系统");
        titleLabel.setPreferredSize(new java.awt.Dimension(300, 50));
        nouthjPanel.add(titleLabel);

        getContentPane().add(nouthjPanel, java.awt.BorderLayout.PAGE_START);

        centerjPanel.setLayout(new java.awt.CardLayout());

        jTable2.setFont(new java.awt.Font("宋体", 0, 18)); // NOI18N
        jTable2.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                "多选", "学号", "姓名", "性别", "出生日期", "数学", "java", "体育"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Boolean.class, java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.Object.class
            };
            boolean[] canEdit = new boolean [] {
                true, false, true, true, true, true, true, true
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jTable2.setRowHeight(45);
        jTable2.setSelectionMode(javax.swing.ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
        jTable2.getTableHeader().setReorderingAllowed(false);
        jTable2.getModel().addTableModelListener(new TableModelListener(){
            public void tableChanged(TableModelEvent e)
            {
                updateDatabase(e);
            }
        });
        jTable2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTable2MouseClicked(evt);
            }
            public void mousePressed(java.awt.event.MouseEvent evt) {
                jTable2MousePressed(evt);
            }
            public void mouseReleased(java.awt.event.MouseEvent evt) {
                jTable2MouseReleased(evt);
            }
        });
        jTable2.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
            public void propertyChange(java.beans.PropertyChangeEvent evt) {
                jTable2PropertyChange(evt);
            }
        });
        jTable2.addVetoableChangeListener(new java.beans.VetoableChangeListener() {
            public void vetoableChange(java.beans.PropertyChangeEvent evt)throws java.beans.PropertyVetoException {
                jTable2VetoableChange(evt);
            }
        });
        jScrollPane2.setViewportView(jTable2);
        if (jTable2.getColumnModel().getColumnCount() > 0) {
            jTable2.getColumnModel().getColumn(0).setResizable(false);
            jTable2.getColumnModel().getColumn(0).setPreferredWidth(40);
            jTable2.getColumnModel().getColumn(1).setResizable(false);
            jTable2.getColumnModel().getColumn(2).setResizable(false);
            jTable2.getColumnModel().getColumn(3).setResizable(false);
            jTable2.getColumnModel().getColumn(4).setResizable(false);
            jTable2.getColumnModel().getColumn(5).setResizable(false);
            jTable2.getColumnModel().getColumn(6).setResizable(false);
            jTable2.getColumnModel().getColumn(7).setResizable(false);
        }

        clearjButton.setText("清空系统数据");
        clearjButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                clearjButtonActionPerformed(evt);
            }
        });

        deleteSelectedjButton.setText("删除所选");
        deleteSelectedjButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                deleteSelectedjButtonActionPerformed(evt);
            }
        });

        allSelectButton.setText("全选");
        allSelectButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                allSelectButtonActionPerformed(evt);
            }
        });

        allUnselectedButton.setText("取消全选");
        allUnselectedButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                allUnselectedButtonActionPerformed(evt);
            }
        });

        addStudentjButton.setText("添加学生");
        addStudentjButton.setPreferredSize(new java.awt.Dimension(100, 45));
        addStudentjButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                addStudentjButtonActionPerformed(evt);
            }
        });

        queryByIdjButton.setText("学号查询");
        queryByIdjButton.setPreferredSize(new java.awt.Dimension(100, 45));
        queryByIdjButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                queryByIdjButtonActionPerformed(evt);
            }
        });

        generateTestDatajButton.setText("重新生成数据");
        generateTestDatajButton.setPreferredSize(new java.awt.Dimension(123, 45));
        generateTestDatajButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                generateTestDatajButtonActionPerformed(evt);
            }
        });

        generateReportjButton.setText("生成成绩报表");
        generateReportjButton.setPreferredSize(new java.awt.Dimension(123, 45));
        generateReportjButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                generateReportjButtonActionPerformed(evt);
            }
        });

        queryByNamejButton.setText("姓名查询");
        queryByNamejButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                queryByNamejButtonActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout managePanelLayout = new javax.swing.GroupLayout(managePanel);
        managePanel.setLayout(managePanelLayout);
        managePanelLayout.setHorizontalGroup(
            managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane2)
            .addGroup(managePanelLayout.createSequentialGroup()
                .addContainerGap()
                .addGroup(managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(managePanelLayout.createSequentialGroup()
                        .addComponent(allSelectButton, javax.swing.GroupLayout.PREFERRED_SIZE, 63, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(allUnselectedButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                    .addComponent(deleteSelectedjButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addGap(58, 58, 58)
                .addComponent(clearjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 134, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(addStudentjButton, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(generateTestDatajButton, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(62, 62, 62)
                .addGroup(managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, managePanelLayout.createSequentialGroup()
                        .addComponent(queryByNamejButton, javax.swing.GroupLayout.PREFERRED_SIZE, 96, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(queryByIdjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 96, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(generateReportjButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        managePanelLayout.setVerticalGroup(
            managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(managePanelLayout.createSequentialGroup()
                .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 413, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(allSelectButton, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(allUnselectedButton, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(queryByIdjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(queryByNamejButton, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(managePanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(deleteSelectedjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 47, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(clearjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 48, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(addStudentjButton, javax.swing.GroupLayout.PREFERRED_SIZE, 48, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(generateTestDatajButton, javax.swing.GroupLayout.PREFERRED_SIZE, 48, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(generateReportjButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addContainerGap())
        );

        centerjPanel.add(managePanel, "card2");

        reportjTable.setFont(new java.awt.Font("宋体", 0, 18)); // NOI18N
        reportjTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                "学号", "姓名", "数学", "java", "体育", "总成绩", "平均成绩"
            }
        ) {
            boolean[] canEdit = new boolean [] {
                false, false, false, false, false, false, false
            };

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        reportjTable.setRowHeight(45);
        reportjTable.getTableHeader().setReorderingAllowed(false);
        reportjTable.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                reportjTableMouseClicked(evt);
            }
        });
        reportjTable.addInputMethodListener(new java.awt.event.InputMethodListener() {
            public void caretPositionChanged(java.awt.event.InputMethodEvent evt) {
            }
            public void inputMethodTextChanged(java.awt.event.InputMethodEvent evt) {
                reportjTableInputMethodTextChanged(evt);
            }
        });
        jScrollPane1.setViewportView(reportjTable);
        if (reportjTable.getColumnModel().getColumnCount() > 0) {
            reportjTable.getColumnModel().getColumn(0).setResizable(false);
            reportjTable.getColumnModel().getColumn(1).setResizable(false);
            reportjTable.getColumnModel().getColumn(2).setResizable(false);
            reportjTable.getColumnModel().getColumn(3).setResizable(false);
            reportjTable.getColumnModel().getColumn(4).setResizable(false);
            reportjTable.getColumnModel().getColumn(5).setResizable(false);
            reportjTable.getColumnModel().getColumn(6).setResizable(false);
        }

        avgjLabel.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        avgjLabel.setText("平均分：0.00");
        avgjLabel.setPreferredSize(new java.awt.Dimension(75, 20));

        mathjLabel.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        mathjLabel.setText("数学：0.0");

        javajLabel.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        javajLabel.setText("java：0.0");

        sportsjLabel.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        sportsjLabel.setText("体育：0.0");

        totaljLabel.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        totaljLabel.setText("总分：0.0");

        jButton1.setText("返回");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jLabel1.setFont(new java.awt.Font("宋体", 1, 18)); // NOI18N
        jLabel1.setText("平均情况：");

        javax.swing.GroupLayout reportPanelLayout = new javax.swing.GroupLayout(reportPanel);
        reportPanel.setLayout(reportPanelLayout);
        reportPanelLayout.setHorizontalGroup(
            reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(reportPanelLayout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, 83, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabel1)
                .addGap(18, 18, 18)
                .addComponent(mathjLabel)
                .addGap(35, 35, 35)
                .addComponent(javajLabel)
                .addGap(30, 30, 30)
                .addComponent(sportsjLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 109, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(totaljLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 119, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(avgjLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 136, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(63, 63, 63))
            .addGroup(reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 920, Short.MAX_VALUE))
        );
        reportPanelLayout.setVerticalGroup(
            reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, reportPanelLayout.createSequentialGroup()
                .addContainerGap(453, Short.MAX_VALUE)
                .addGroup(reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(reportPanelLayout.createSequentialGroup()
                        .addGroup(reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 49, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(mathjLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 49, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addContainerGap())
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, reportPanelLayout.createSequentialGroup()
                        .addGroup(reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 47, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(javajLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 50, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(sportsjLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 51, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(totaljLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 51, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(avgjLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 51, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(24, 24, 24))))
            .addGroup(reportPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(reportPanelLayout.createSequentialGroup()
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 448, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGap(0, 80, Short.MAX_VALUE)))
        );

        centerjPanel.add(reportPanel, "card1");

        getContentPane().add(centerjPanel, java.awt.BorderLayout.CENTER);

        pack();
    }// </editor-fold>//GEN-END:initComponents
    public void updateTable() {
        JdbcTemplate template=new JdbcTemplate(JDBCUtil.getDatasource());
        String sql="select * from student";       
        
        List<Student> list = template.query(sql,new BeanPropertyRowMapper<Student>(Student.class));
        
        DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
        Object[] str = new Object[8];
        for(Student e : list){
            str[0]=true;
            str[1]=e.getId();
            str[2]=e.getName();
            str[3]=e.getGender();
            str[4]=e.getBirthday();
            str[5]=e.getMath();
            str[6]=e.getJava();
            str[7]=e.getSports();
            cartModel.addRow(str);       
        }            
    }
    
    private void addStudentjButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_addStudentjButtonActionPerformed
        //添加单个学生信息
  
        Student stu=sdi.getStudent();
        JDialog jd = new JDialog(this,"添加学生");
        jd.add(new JLabel("123"));
        final JTextField jtf1,jtf2,jtf3,jtf4,jtf5,jtf6;
        final JLabel jlinfo;
        JLabel jl1 = new JLabel("姓    名：");
        jtf1 = new JTextField(16);
        JLabel jl2 = new JLabel("性    别：");
        jtf2 = new JTextField(16);
        JLabel jl3 = new JLabel("出生日期(xxxx-xx-xx)：");
        jtf3 = new JTextField(16);

        JPanel jp = new JPanel(new GridLayout(6,2));
        jp.add(jl1);
        jp.add(jtf1);
        jp.add(jl2);
        jp.add(jtf2);
        jp.add(jl3);
        jp.add(jtf3);
        JButton jb = new JButton("确认输入");
        jlinfo = new JLabel("请输入学生信息，学号自动生成",JLabel.CENTER);
        jb.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                
                String id = stu.getId();
                String name = jtf1.getText();
                String gender = jtf2.getText();
                String birthday = jtf3.getText();
                
                String newId;
                Long temp = Long.valueOf(id);
                temp+=1;
                newId = temp.toString();
                                
                Student s = new Student(newId, name, gender, birthday, 0.0, 0.0, 0.0, 0.0);
                if(sdi.addStudent(s)==false){
                    return;
                }
                DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
                Object[] str = new Object[]{true,s.getId(),s.getName(),s.getGender(),s.getBirthday(),0,0,0};
                cartModel.addRow(str);
                JOptionPane.showMessageDialog(jd, "学生： 学号"+newId+" ,姓名："+name+" ,性别："+gender+" ,出生日期："+birthday+"     添加成功！");
            }
        });
        
        //JOptionPane.showInputDialog();
        jd.add(jp);
        jd.add(jlinfo,BorderLayout.NORTH);
        jd.add(jb,BorderLayout.SOUTH);
        jd.pack();
        jd.setModal(true);
        jd.setVisible(true);
        
        //updateTable();

               
        
        
    }//GEN-LAST:event_addStudentjButtonActionPerformed
    
    private void queryByIdjButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_queryByIdjButtonActionPerformed
        //查询学生信息，支持模糊查找
        
        QueryJFrame queryJF = new QueryJFrame();
        this.setVisible(true);
        queryJF.start();

    }//GEN-LAST:event_queryByIdjButtonActionPerformed
    public void updateDatabase(TableModelEvent e){
        sdi.updateDatabase(e,jTable2.getModel());
    }
    

    private void reportjTableMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_reportjTableMouseClicked

    }//GEN-LAST:event_reportjTableMouseClicked

    private void reportjTableInputMethodTextChanged(java.awt.event.InputMethodEvent evt) {//GEN-FIRST:event_reportjTableInputMethodTextChanged

    }//GEN-LAST:event_reportjTableInputMethodTextChanged


    private void generateTestDatajButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_generateTestDatajButtonActionPerformed
        //产生测试数据       
        clearjButtonActionPerformed(evt);
        sdi.getTestData((DefaultTableModel) jTable2.getModel());                                          
    }//GEN-LAST:event_generateTestDatajButtonActionPerformed

    private void allUnselectedButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_allUnselectedButtonActionPerformed

        for (int i = 0; i < jTable2.getRowCount(); i++) {
            if (jTable2.getValueAt(i, 0).equals(true)) {
                jTable2.setValueAt(false, i, 0);
            }
        }
    }//GEN-LAST:event_allUnselectedButtonActionPerformed

    private void allSelectButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_allSelectButtonActionPerformed
        TableModel cartModel = jTable2.getModel();
        for (int i = 0; i < jTable2.getRowCount(); i++) {
            if (jTable2.getValueAt(i, 0).equals(false)) {
                jTable2.setValueAt(true, i, 0);                
            }
        }
    }//GEN-LAST:event_allSelectButtonActionPerformed

    private void deleteSelectedjButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_deleteSelectedjButtonActionPerformed
        List<Object[]> batchArgs=new ArrayList<>();
        DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
        for (int i = jTable2.getRowCount() - 1; i > -1; i--) {
            if (jTable2.getValueAt(i, 0).equals(true)) {
                jTable2.setValueAt(false, i, 0);
                String id = String.valueOf(jTable2.getValueAt(i, 1));
                String name = String.valueOf(jTable2.getValueAt(i, 2));
                String gender = String.valueOf(jTable2.getValueAt(i, 3));
                String birthday = jTable2.getValueAt(i, 4).toString();
                double math = Double.valueOf(String.valueOf(jTable2.getValueAt(i, 5)));
                double java = Double.valueOf(String.valueOf(jTable2.getValueAt(i, 6)));
                double sports = Double.valueOf(String.valueOf(jTable2.getValueAt(i, 7)));
                double totalScore = math+java+sports;
                batchArgs.add(new Object[]{id});                
                cartModel.removeRow(i);
            }
        }
        sdi.deleteSelected(batchArgs);
    }//GEN-LAST:event_deleteSelectedjButtonActionPerformed

    private void clearjButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_clearjButtonActionPerformed
        DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
        cartModel.setRowCount(0);
        sdi.clearAll();
    }//GEN-LAST:event_clearjButtonActionPerformed
    

    private void jTable2PropertyChange(java.beans.PropertyChangeEvent evt) {//GEN-FIRST:event_jTable2PropertyChange

    }//GEN-LAST:event_jTable2PropertyChange
   
    Date last, now;
    private void jTable2MouseReleased(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable2MouseReleased
        now = new Date();
        DefaultTableModel cartModel = (DefaultTableModel) jTable2.getModel();
        int selectionRow = jTable2.getSelectedRow();
        if (now.getTime() - last.getTime() > 500) {
            int num = JOptionPane.showConfirmDialog(null, "删除该学生？", "提示", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
            switch (num) {
                case JOptionPane.YES_OPTION:
                    jTable2.setValueAt(false, selectionRow, 0);
                    String id = (String)jTable2.getValueAt(selectionRow, 1); //获取学号
                    sdi.deleteById(id);
                    cartModel.removeRow(selectionRow);
                    break;
                case JOptionPane.NO_OPTION:
                    break;
            }
        }
    }//GEN-LAST:event_jTable2MouseReleased

    private void jTable2MousePressed(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable2MousePressed
        last = new Date();
    }//GEN-LAST:event_jTable2MousePressed

    private void jTable2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable2MouseClicked
        // 右键点击
        try{
            if(evt.getButton() == MouseEvent.BUTTON3) {
                int selectionRow = jTable2.getSelectedRow();
                if (selectionRow != -1) {
                    TableModel model = jTable2.getModel();
                        JFrame frame = new JFrame("成绩柱状图");
                        frame.setLayout(new BorderLayout());
                        String name =(String)model.getValueAt(selectionRow, 2);
                        double math = (double)model.getValueAt(selectionRow, 5);
                        double java = (double)model.getValueAt(selectionRow, 6);
                        double sports=(double)model.getValueAt(selectionRow, 7);
                        frame.add(new BarChart(name, math, java, sports).getChartPanel()); 
                        frame.setBounds(0, 0, 800, 500);
                        frame.setVisible(true);
                }
            }
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, "数据存在错误");
        }

    }//GEN-LAST:event_jTable2MouseClicked

    private void generateReportjButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_generateReportjButtonActionPerformed
        //生成报表
        CardLayout card = (CardLayout) centerjPanel.getLayout();
        card.show(centerjPanel, "card1");
        titleLabel.setText("学生学习成绩报表");
           
        DefaultTableModel cartModel = (DefaultTableModel) reportjTable.getModel();
        List<Student> list = sdi.query();
                    
        DecimalFormat df = new DecimalFormat(".00");
        double avgMath=0,avgJava=0,avgSports=0,avgTotal=0,avgScore=0,t;
        
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("第一页");
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("学号");
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("数学");
        row.createCell(3).setCellValue("java");
        row.createCell(4).setCellValue("体育");
        row.createCell(5).setCellValue("总分");
        row.createCell(6).setCellValue("平均分");
        int i=1;
        Object[] str = new Object[7]; 
        for(Student e : list){
            str[0]=e.getId();
            str[1]=e.getName();
            str[2]=e.getMath();
            str[3]=e.getJava();
            str[4]=e.getSports();
            str[5]=e.getTotalScore();
            if(str[5]!=null&&(!str[5].equals(0))){
                t=(double)str[5]/3.0;
            }else{
                t = 0;
            }
            
            str[6]=df.format(t);
            cartModel.addRow(str);
            avgMath+=(double)str[2];
            avgJava+=(double)str[3];
            avgSports+=(double)str[4];
            avgTotal+=(double)str[5];
            avgScore+=t; 
            
            row = sheet.createRow(i++);
            for(int j = 0; j < 7; j++){
                row.createCell(j).setCellValue(str[j].toString());
            }          
        }
        DecimalFormat df1 = new DecimalFormat(".0");
        double num=Double.valueOf(list.size());
        String []avgStr=new String[]{df1.format(avgMath/num),df1.format(avgJava/num),df1.format(avgSports/num),df1.format(avgTotal/num),df.format(avgScore/num)};
        mathjLabel.setText("数学："+avgStr[0]);
        javajLabel.setText("java："+avgStr[1]);
        sportsjLabel.setText("体育："+avgStr[2]);
        totaljLabel.setText("总分："+avgStr[3]);
        avgjLabel.setText("平均分："+avgStr[4]);
        
        row = sheet.createRow(i++);
        row.createCell(0).setCellValue("平均成绩：");
        for(int j = 0; j < 5; j++){
            row.createCell(j+2).setCellValue(avgStr[j]);
        }        
        try (FileOutputStream out = new FileOutputStream("d:\\学习成绩表.xlsx")) {
            
            wb.write(out);
            out.flush();
            out.close();
            wb.close();
        }catch (IOException e) {
            JOptionPane.showMessageDialog(null, "生成报表时发生错误");
        }   
        JOptionPane.showMessageDialog(null, "已生成学生成绩表");
    }//GEN-LAST:event_generateReportjButtonActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        CardLayout card = (CardLayout) centerjPanel.getLayout();
        card.show(centerjPanel, "card2"); 
        DefaultTableModel cartModel = (DefaultTableModel) reportjTable.getModel();
        cartModel.setRowCount(0);
    }//GEN-LAST:event_jButton1ActionPerformed
    
  
    private void jTable2VetoableChange(java.beans.PropertyChangeEvent evt)throws java.beans.PropertyVetoException {//GEN-FIRST:event_jTable2VetoableChange
        // TODO add your handling code here:
    }//GEN-LAST:event_jTable2VetoableChange

    private void queryByNamejButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_queryByNamejButtonActionPerformed
        //  通过姓名查找学生，支持模糊查找
        
            String targetName = JOptionPane.showInputDialog("请输入学生姓名：");
            if(targetName.equals("")){
                JOptionPane.showMessageDialog(this, "输入为空！");
                return;
            }
            List<Student> list = sdi.query();
            List<Student> targetStudents = new ArrayList<>();
        for (Student student : list) {
            if(student.getName().contains(targetName)){
                targetStudents.add(student);
            }else if(student.getName().equals(targetName)){
                targetStudents.add(student);
            }
        }
        if(targetStudents.size()==0){
            JOptionPane.showMessageDialog(this, "查无此学生信息！");
        }else {
            JDialog jd = new JDialog(this, "查找结果");
            JPanel jp = new JPanel(new GridLayout(targetStudents.size(),1));
            JLabel[] jls = new JLabel[targetStudents.size()];
            for(int i = 0;i<targetStudents.size();i++){
                jls[i] = new JLabel(targetStudents.get(i).toString());
                jp.add(jls[i]);
            }
            jd.add(jp);
            jd.pack();
            jd.setVisible(true);
        
        }
    }//GEN-LAST:event_queryByNamejButtonActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(SystemJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(SystemJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(SystemJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(SystemJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new SystemJFrame().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton addStudentjButton;
    private javax.swing.JButton allSelectButton;
    private javax.swing.JButton allUnselectedButton;
    private javax.swing.JLabel avgjLabel;
    private javax.swing.JPanel centerjPanel;
    private javax.swing.JButton clearjButton;
    private javax.swing.JButton deleteSelectedjButton;
    private javax.swing.JButton generateReportjButton;
    private javax.swing.JButton generateTestDatajButton;
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JTable jTable2;
    private javax.swing.JLabel javajLabel;
    private javax.swing.JPanel managePanel;
    private javax.swing.JLabel mathjLabel;
    private javax.swing.JPanel nouthjPanel;
    private javax.swing.JButton queryByIdjButton;
    private javax.swing.JButton queryByNamejButton;
    private javax.swing.JPanel reportPanel;
    private javax.swing.JTable reportjTable;
    private javax.swing.JLabel sportsjLabel;
    private javax.swing.JLabel titleLabel;
    private javax.swing.JLabel totaljLabel;
    // End of variables declaration//GEN-END:variables
}
